﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Utils;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace Dao
{
    public class ItemsDao
    {
        DBConnection dbConnect;
        public ItemsDao()
        {
            dbConnect = new DBConnection();
        }

        public DataTable getAllItems()
        {
            string sql = "select * from items i join Category c on c.CateId = i.CateId";
            return dbConnect.executeSelectQuery(sql);
        }

        public DataTable getAllCategory()
        {
            string sql = "select * from category";
            return dbConnect.executeSelectQuery(sql);
        }

        public DataTable getItemById(string id)
        {
            string sql = "select * from items where ItemId =" + id + "";
            return dbConnect.executeSelectQuery(sql);
        }

        public bool insertItems(Items item)
        {
            string query = string.Format(@"insert into items
            (itemTitle , itemDescription , bidStatus , bidStartDate , bidEndDate ,
             BidIncremenent , minimumbid ,cateId , itemImage , userId ) 
             values
            (@itemTitle ,@itemDescription ,@bidStatus ,@bidStartDate , @bidEndDate ,
             @bidIncremenent ,@minimumbid ,@cateId ,@itemImage ,@userId)");
            SqlParameter[] sqlParameters = new SqlParameter[10];

            sqlParameters[0] = new SqlParameter("@itemTitle", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(item.ItemTitle);

            sqlParameters[1] = new SqlParameter("@itemDescription", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(item.ItemDescription);

            sqlParameters[2] = new SqlParameter("@bidStatus", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(item.BidStatus);

            sqlParameters[3] = new SqlParameter("@bidStartDate", SqlDbType.DateTime);
            sqlParameters[3].Value = Convert.ToString(item.BidStartDate);

            sqlParameters[4] = new SqlParameter("@bidEndDate", SqlDbType.DateTime);
            sqlParameters[4].Value = Convert.ToString(item.BidEndDate);

            sqlParameters[5] = new SqlParameter("@bidIncremenent", SqlDbType.NVarChar);
            sqlParameters[5].Value = Convert.ToString(item.MidIncremenent);

            sqlParameters[6] = new SqlParameter("@minimumbid", SqlDbType.NVarChar);
            sqlParameters[6].Value = Convert.ToString(item.Minimumbid);

            sqlParameters[7] = new SqlParameter("@cateId", SqlDbType.Int);
            sqlParameters[7].Value = Convert.ToString(item.CateId);

            sqlParameters[8] = new SqlParameter("@itemImage", SqlDbType.NVarChar);
            sqlParameters[8].Value = Convert.ToString(item.ItemImage);

            sqlParameters[9] = new SqlParameter("@userId", SqlDbType.Int);
            sqlParameters[9].Value = Convert.ToString(item.UserId);

            return dbConnect.executeInsertQuery(query, sqlParameters);
        }

        public bool updateItems(Items item)
        {
            string query = string.Format(@"update items
             set itemTitle = @itemTitle , itemDescription =@itemDescription , 
                 bidStatus =@bidStatus  , BidEndDate = @bidEndDate , 
                 bidIncremenent =@bidIncremenent , minimumbid =@minimumbid ,
                 cateId =@cateId , itemImage =@itemImage , userId =@userId where itemId =@itemId");
            SqlParameter[] sqlParameters = new SqlParameter[10];

            sqlParameters[0] = new SqlParameter("@itemTitle", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(item.ItemTitle);

            sqlParameters[1] = new SqlParameter("@itemDescription", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(item.ItemDescription);

            sqlParameters[2] = new SqlParameter("@bidStatus", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(item.BidStatus);

            sqlParameters[3] = new SqlParameter("@bidEndDate", SqlDbType.DateTime);
            sqlParameters[3].Value = Convert.ToString(item.BidEndDate);

            sqlParameters[4] = new SqlParameter("@bidIncremenent", SqlDbType.NVarChar);
            sqlParameters[4].Value = Convert.ToString(item.MidIncremenent);

            sqlParameters[5] = new SqlParameter("@minimumbid", SqlDbType.NVarChar);
            sqlParameters[5].Value = Convert.ToString(item.Minimumbid);

            sqlParameters[6] = new SqlParameter("@cateId", SqlDbType.Int);
            sqlParameters[6].Value = Convert.ToString(item.CateId);

            sqlParameters[7] = new SqlParameter("@itemImage", SqlDbType.NVarChar);
            sqlParameters[7].Value = Convert.ToString(item.ItemImage);

            sqlParameters[8] = new SqlParameter("@userId", SqlDbType.Int);
            sqlParameters[8].Value = Convert.ToString(item.UserId);

            sqlParameters[9] = new SqlParameter("@itemId", SqlDbType.Int);
            sqlParameters[9].Value = Convert.ToString(item.ItemId);

            return dbConnect.executeUpdateQuery(query, sqlParameters);
        }

        public bool updateItemsNotImage(Items item)
        {
            string query = string.Format(@"update items
             set itemTitle = @itemTitle , itemDescription =@itemDescription , 
                 bidStatus =@bidStatus  , bidStartDate =@bidStartDate , 
                 bidIncremenent =@bidIncremenent , minimumbid =@minimumbid ,
                 cateId =@cateId where itemId =@itemId");
            SqlParameter[] sqlParameters = new SqlParameter[8];

            sqlParameters[0] = new SqlParameter("@itemTitle", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(item.ItemTitle);

            sqlParameters[1] = new SqlParameter("@itemDescription", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(item.ItemDescription);

            sqlParameters[2] = new SqlParameter("@bidStatus", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(item.BidStatus);

            sqlParameters[3] = new SqlParameter("@bidStartDate", SqlDbType.DateTime);
            sqlParameters[3].Value = Convert.ToString(item.BidStartDate);

            sqlParameters[4] = new SqlParameter("@bidIncremenent", SqlDbType.NVarChar);
            sqlParameters[4].Value = Convert.ToString(item.MidIncremenent);

            sqlParameters[5] = new SqlParameter("@minimumbid", SqlDbType.NVarChar);
            sqlParameters[5].Value = Convert.ToString(item.Minimumbid);

            sqlParameters[6] = new SqlParameter("@cateId", SqlDbType.Int);
            sqlParameters[6].Value = Convert.ToString(item.CateId);

            sqlParameters[7] = new SqlParameter("@itemId", SqlDbType.Int);
            sqlParameters[7].Value = Convert.ToString(item.ItemId);

            return dbConnect.executeUpdateQuery(query, sqlParameters);
        }

        public bool deleteItems(int id)
        {
            string query = string.Format("delete items  where itemId =@id");
            SqlParameter[] sqlParameters = new SqlParameter[1];

            sqlParameters[0] = new SqlParameter("@id", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToString(id);

            return dbConnect.executeDeleteQuery(query, sqlParameters);
        }

    }
}
